package com.bj58.groupbuy.action.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.bj58.groupbuy.action.db.DBConfig;
import com.bj58.groupbuy.action.db.DBConnectionPool;
import com.bj58.groupbuy.action.db.JdbcUitl;
import com.bj58.groupbuy.action.service.PvMapService;

public class PvMapServiceImpl implements PvMapService {
	//获得各市的数据infobright_26_data_bi
	@Override
	public String getCityPcPv(String date,String pvtype) throws Exception {
		String sql = "SELECT  city_name, "+pvtype+"_pv from t_netflow_allcity_pv_uv WHERE page_type='all' and stat_date='"+date+"'";
		
		Connection con = DBConnectionPool.getConnection(DBConfig.db_131_data_bi.getIntType());
		String josnstring="";
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				  
				josnstring +=""+rs.getString(1)+","+rs.getString(2)+",";
			}
			if(josnstring.length()>2)
			josnstring =josnstring.substring(0,josnstring.length()-2);
			//josnstring.substring(0,josnstring.length()-2);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return josnstring;
	}
	//获得各省的数据infobright_26_data_bi
	@Override
	public String getProvincePcPv(String date,String pvtype) throws Exception {
		String sql = "SELECT province_name,sum("+pvtype+"_pv) as pv from t_netflow_allcity_pv_uv WHERE stat_date='"+date+"' and page_type='all' and province_name not in ('重庆','上海','北京','天津','深圳','台湾','香港','澳门') GROUP BY province_name";
		
		Connection con = DBConnectionPool.getConnection(DBConfig.db_131_data_bi.getIntType());
		String josnstring="";
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				  
				josnstring +=""+rs.getString(1)+","+rs.getString(2)+",";
			}
		
			//josnstring.substring(0,josnstring.length()-2);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return josnstring;
	}
	@Override
	public String getMaxdate() throws Exception {
		// TODO Auto-generated method stub
		String sql = "SELECT MAX(stat_date) from t_netflow_allcity_pv_uv";
		
		Connection con = DBConnectionPool.getConnection(DBConfig.db_131_data_bi.getIntType());
		String maxdate="";
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				  
				maxdate +=rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return maxdate;
	}
}
